package com.whpost.service.dao.cwqf;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.whpost.service.entity.cwqf.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author YinZhuoyi
 * @since 2025-08-22
 */
public interface VwCwqfMapper extends BaseMapper<VwCwqf> {

    @Select("SELECT jg_main.code AS jb2_code, jg_main.name AS jb2_name, vw.data_source AS zy_name, SUM(NVL(vw.yqqf, 0)) AS overdueTotal, SUM(NVL(vw.hj, 0)) AS totalAmount, SUM(NVL(vw.qnqf, 0)) AS currentTermTotal FROM hs_jg jg_main LEFT JOIN VW_CWQf vw ON vw.jg LIKE jg_main.code || '%' AND vw.rq >= TO_DATE(#{date}, 'YYYY-MM-DD') AND vw.rq < TO_DATE(#{date}, 'YYYY-MM-DD')+1 WHERE jg_main.jb = '2' and jg_main.zybz = '1' GROUP BY jg_main.code, jg_main.name, vw.data_source, jg_main.xh ORDER BY jg_main.xh")
    List<Jb2FeeSummary> getJb2QfSummary(@Param("date") String date);

    @Select("SELECT " +
            "jg_main.code AS jb3_code, " +
            "jg_main.name AS jb3_name, " +
            "vw.data_source AS zy_name, " +  // 数据来源分类
            "SUM(NVL(vw.yqqf, 0)) AS overdueTotal, " +
            "SUM(NVL(vw.hj, 0)) AS totalAmount, " +
            "SUM(NVL(vw.qnqf, 0)) AS currentTermTotal " +
            "FROM hs_jg jg_main " +
            "LEFT JOIN VW_CWQf vw " +
            "    ON vw.jg LIKE jg_main.code || '%' " +
            "    AND vw.rq >= TO_DATE(#{date}, 'YYYY-MM-DD') " +
            "    AND vw.rq < TO_DATE(#{date}, 'YYYY-MM-DD')+1 " +
            "WHERE jg_main.zybz = '1' and jg_main.jb = '3' and jg_main.pcode = #{pcode} " +
            "GROUP BY jg_main.code, jg_main.name, vw.data_source " +  // 新增data_source到GROUP BY
            "ORDER BY jg_main.code, vw.data_source")
    List<Jb3FeeSummary> getJb3QfSummary(@Param("date") String date,@Param("pcode") String pcode);


    /**
     * 按专业统计两个表的金额数据
     * @param rq 日期参数（格式：yyyy-MM-dd）
     * @return 统计结果列表，包含hj、yqqf、zy、data_source字段
     */
    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy as name,data_source from vw_cwqf where rq >= TO_DATE(#{rq}, 'YYYY-MM-DD') AND rq < TO_DATE(#{rq}, 'YYYY-MM-DD')+1 group by zy,data_source order by data_source")
    List<Zyqf> getZyQfSummary(@Param("rq") String rq);

    /**
     * 按专业统计两个表的金额数据
     * @return
     */
    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy as name,data_source from vw_cwqf where is_new='1' group by zy,data_source order by data_source")
    List<Zyqf> getZyQfSummaryIsNew();

    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy,data_source,pcode,pname as name  from vw_cwqf where rq >= TO_DATE(#{rq}, 'YYYY-MM-DD') AND rq < TO_DATE(#{rq}, 'YYYY-MM-DD')+1 and zy=#{zy} group by zy,data_source,pcode,pname "+
            "ORDER BY DECODE(pname,\n" +
            "               '洪山',1,\n" +
            "               '武昌',2,\n" +
            "               '江岸',3,\n" +
            "               '江汉',4,\n" +
            "               '硚口',5,\n" +
            "               '汉阳',6,\n" +
            "               '青山',7,\n" +
            "               '东西湖',8,\n" +
            "               '汉南',9,\n" +
            "               '江夏',10,\n" +
            "               '蔡甸',11,\n" +
            "               '黄陂',12,\n" +
            "               '新洲',13,\n" +
            "               '邮政业务部',14,\n" +
            "               '寄递业务部',15)")
    List<Zyqf> getZyQfSummaryZy(@Param("rq") String rq,@Param("zy") String zy);

    /**
     * 指定专业,区局统计两个表的金额数据（各区的下属机构）
     * @return
     */
    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy,data_source,jg_name as name,jg from vw_cwqf where rq >= TO_DATE(#{rq}, 'YYYY-MM-DD') AND rq < TO_DATE(#{rq}, 'YYYY-MM-DD')+1 and zy=#{zy} and pcode=#{pcode} group by zy,data_source,jg_name,jg ORDER BY jg")
    List<Zyqf> getZyQfSummaryZy2(@Param("rq") String rq,@Param("zy") String zy,@Param("pcode") String pcode);

    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy,data_source,kh_code,kh_name as name from vw_cwqf where rq >= TO_DATE(#{rq}, 'YYYY-MM-DD') AND rq < TO_DATE(#{rq}, 'YYYY-MM-DD')+1 and zy=#{zy} and jg=#{jg} group by zy,data_source,kh_code,kh_name ORDER BY jg")
    List<Zyqf> getZyQfSummaryZy3(@Param("rq") String rq,@Param("zy") String zy,@Param("jg") String jg);

    /**
     * 指定专业统计两个表的金额数据（各区）
     * @return
     */
    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy,data_source,pcode,pname as name from vw_cwqf where is_new='1' and zy=#{zy} group by zy,data_source,pcode,pname "+
            "ORDER BY DECODE(pname,\n" +
            "               '洪山',1,\n" +
            "               '武昌',2,\n" +
            "               '江岸',3,\n" +
            "               '江汉',4,\n" +
            "               '硚口',5,\n" +
            "               '汉阳',6,\n" +
            "               '青山',7,\n" +
            "               '东西湖',8,\n" +
            "               '汉南',9,\n" +
            "               '江夏',10,\n" +
            "               '蔡甸',11,\n" +
            "               '黄陂',12,\n" +
            "               '新洲',13,\n" +
            "               '邮政业务部',14,\n" +
            "               '寄递业务部',15)")
    List<Zyqf> getZyQfSummaryZyIsNew(@Param("zy") String zy);

    /**
     * 指定专业,区局统计两个表的金额数据（各区的下属机构）
     * @return
     */
    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy,data_source,jg_name as name,jg from vw_cwqf where is_new='1' and zy=#{zy} and pcode=#{pcode} group by zy,data_source,jg_name,jg ORDER BY jg")
    List<Zyqf> getZyQfSummaryZy2IsNew(@Param("zy") String zy,@Param("pcode") String pcode);

    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,zy,data_source,kh_code,kh_name as name from vw_cwqf where is_new='1' and zy=#{zy} and jg=#{jg} group by zy,data_source,kh_code,kh_name ORDER BY jg")
    List<Zyqf> getZyQfSummaryZy3IsNew(@Param("zy") String zy,@Param("jg") String jg);

    /**
     * 统计各区各专业逾期欠费数据
     * @param rq 日期参数（格式：yyyy-MM-dd）
     */
    @Select("SELECT  name,\n" +
            "        bk, fx, hj, jy, ds, gj, kdbg, bzkd, wl,\n" +
            "        NVL(bk,0)+NVL(fx,0)+NVL(hj,0)+NVL(jy,0)+NVL(ds,0)+\n" +
            "        NVL(gj,0)+NVL(kdbg,0)+NVL(bzkd,0)+NVL(wl,0) AS total\n" +
            "FROM (\n" +
            "    SELECT name,\n" +
            "           MAX(CASE WHEN zy='报刊'     THEN yqqf END) AS bk,\n" +
            "           MAX(CASE WHEN zy='分销'     THEN yqqf END) AS fx,\n" +
            "           MAX(CASE WHEN zy='函件'     THEN yqqf END) AS hj,\n" +
            "           MAX(CASE WHEN zy='集邮'     THEN yqqf END) AS jy,\n" +
            "           MAX(CASE WHEN zy='电子商务'     THEN yqqf END) AS ds,\n" +
            "           MAX(CASE WHEN zy='国际港澳台'     THEN yqqf END) AS gj,\n" +
            "           MAX(CASE WHEN zy='快递包裹' THEN yqqf END) AS kdbg,\n" +
            "           MAX(CASE WHEN zy='特快专递' THEN yqqf END) AS bzkd,\n" +
            "           MAX(CASE WHEN zy='物流'     THEN yqqf END) AS wl\n" +
            "    FROM (\n" +
            "        SELECT g2.name AS name,\n" +
            "               q.zy,\n" +
            "               SUM(q.yqqf) AS yqqf\n" +
            "        FROM vw_cwqf q\n" +
            "        JOIN hs_jg g3 ON q.jg = g3.code\n" +
            "        JOIN hs_jg g2 ON g3.pcode = g2.code\n" +
            "        WHERE g3.jb = '3'\n" +
            "          AND g2.jb = '2'\n" +
            "          AND TRUNC(rq) >= TO_DATE(#{rq}, 'YYYY-MM-DD')\n" +
            "          AND TRUNC(rq) < TO_DATE(#{rq}, 'YYYY-MM-DD')+1\n" +
            "        GROUP BY g2.name, q.zy\n" +
            "    ) t\n" +
            "    GROUP BY name\n" +
            ")\n" +
            "ORDER BY DECODE(name,\n" +
            "               '洪山',1,\n" +
            "               '武昌',2,\n" +
            "               '江岸',3,\n" +
            "               '江汉',4,\n" +
            "               '硚口',5,\n" +
            "               '汉阳',6,\n" +
            "               '青山',7,\n" +
            "               '东西湖',8,\n" +
            "               '汉南',9,\n" +
            "               '江夏',10,\n" +
            "               '蔡甸',11,\n" +
            "               '黄陂',12,\n" +
            "               '新洲',13,\n" +
            "               '邮政业务部',14,\n" +
            "               '寄递业务部',15)")
    List<Qsyqqf> getQsyqqfSummary(@Param("rq") String rq);

    @Select("SELECT  name,\n" +
            "        bk, fx, hj, jy, ds, gj, kdbg, bzkd, wl,\n" +
            "        NVL(bk,0)+NVL(fx,0)+NVL(hj,0)+NVL(jy,0)+NVL(ds,0)+\n" +
            "        NVL(gj,0)+NVL(kdbg,0)+NVL(bzkd,0)+NVL(wl,0) AS total\n" +
            "FROM (\n" +
            "    SELECT name,\n" +
            "           MAX(CASE WHEN zy='报刊'     THEN yqqf END) AS bk,\n" +
            "           MAX(CASE WHEN zy='分销'     THEN yqqf END) AS fx,\n" +
            "           MAX(CASE WHEN zy='函件'     THEN yqqf END) AS hj,\n" +
            "           MAX(CASE WHEN zy='集邮'     THEN yqqf END) AS jy,\n" +
            "           MAX(CASE WHEN zy='电子商务'     THEN yqqf END) AS ds,\n" +
            "           MAX(CASE WHEN zy='国际港澳台'     THEN yqqf END) AS gj,\n" +
            "           MAX(CASE WHEN zy='快递包裹' THEN yqqf END) AS kdbg,\n" +
            "           MAX(CASE WHEN zy='特快专递' THEN yqqf END) AS bzkd,\n" +
            "           MAX(CASE WHEN zy='物流'     THEN yqqf END) AS wl\n" +
            "    FROM (\n" +
            "        SELECT g2.name AS name,\n" +
            "               q.zy,\n" +
            "               SUM(q.yqqf) AS yqqf\n" +
            "        FROM vw_cwqf q\n" +
            "        JOIN hs_jg g3 ON q.jg = g3.code\n" +
            "        JOIN hs_jg g2 ON g3.pcode = g2.code\n" +
            "        WHERE g3.jb = '3'\n" +
            "          AND g2.jb = '2'\n" +
            "          AND q.is_new='1'\n" +
            "        GROUP BY g2.name, q.zy\n" +
            "    ) t\n" +
            "    GROUP BY name\n" +
            ")\n" +
            "ORDER BY DECODE(name,\n" +
            "               '洪山',1,\n" +
            "               '武昌',2,\n" +
            "               '江岸',3,\n" +
            "               '江汉',4,\n" +
            "               '硚口',5,\n" +
            "               '汉阳',6,\n" +
            "               '青山',7,\n" +
            "               '东西湖',8,\n" +
            "               '汉南',9,\n" +
            "               '江夏',10,\n" +
            "               '蔡甸',11,\n" +
            "               '黄陂',12,\n" +
            "               '新洲',13,\n" +
            "               '邮政业务部',14,\n" +
            "               '寄递业务部',15)")
    List<Qsyqqf> getQsyqqfSummaryIsNew();

    /**
     * 按客户统计两个表的金额数据
     * @param rq 日期参数（格式：yyyy-MM-dd）
     * @return 统计结果列表，包含hj、yqqf、kh_code、data_source、kh_name字段
     */
    @Select("select sum(hj) as hj,sum(yqqf) as yqqf,kh_code,(select customer_name from tb_crm_customer where tb_crm_customer.customer_code=kh_code and rownum=1) as kh_name,data_source from vw_cwqf where rq >= TO_DATE(#{rq}, 'YYYY-MM-DD') and rq < TO_DATE(#{rq}, 'YYYY-MM-DD')+1 group by kh_code,data_source order by data_source")
    List<Khqf> getKhQfSummary(@Param("rq") String rq);

    /**
     * 按客户经理统计两个表的金额数据
     * @param rq 日期参数（格式：yyyy-MM-dd）
     * @return 统计结果列表，包含hj、yqqf、kh_code、data_source、kh_name字段
     */
    @Select("SELECT \n" +
            "    kjl.czy_name AS khjl_name,\n" +
            "    kjl.czy_code AS khjl_code,\n" +
            "    SUM(v.hj) AS hj,\n" +
            "    SUM(v.yqqf) AS yqqf\n" +
            "FROM vw_cwqf v\n" +
            "JOIN TB_CRM_CUSTOMER c \n" +
            "    ON v.kh_code = c.customer_code\n" +
            "JOIN t_cwqf_khjl_kh kjl \n" +
            "    ON c.party_code = kjl.party_code\n" +
            "WHERE v.rq >= TO_DATE(#{rq}, 'YYYY-MM-DD')\n" +
            "AND v.rq < TO_DATE(#{rq}, 'YYYY-MM-DD')+1\n" +
            "GROUP BY kjl.czy_code\n" +
            "ORDER BY kjl.czy_code;")
    List<Khqf> getKhjlQfSummary(@Param("rq") String rq);
}
